
clear all

// This file creates a county of the number of school districts per county using data from the National Center for Education Statistics (NCES). 

// Download 2017 Geographic Relationship Files called grf17_lea_county, here: https://nces.ed.gov/programs/edge/geographic/relationshipfiles

import excel "$InputPath/grf17_lea_county.xlsx", clear firstrow

gen fstate=substr(STCOUNTY,1,2)
gen fcounty=substr(STCOUNTY,3,3)

destring fstate fcounty, replace

// drop technical schools, military schools, special schools
gen technical =strpos(NAME_LEA17,"Technical")

gen military =strpos(NAME_LEA17,"Defense ")+strpos(NAME_LEA17,"Marine ")

gen special= strpos(NAME_LEA17,"Special")

drop if technical>0 | military>0 | special>0 // 30 school districts

// county has one school district 
egen leatag=tag(LEAID fstate fcounty) // note, multiple rows per LEAID if split across counties
egen nschool_per_county=total(leatag), by(fstate fcounty)


gen leatag_nosplit=leatag 
replace leatag_nosplit=0 if COUNT>1
egen nschool_per_county_nosplit=total(leatag_nosplit), by(fstate fcounty)


keep nschool_per_county nschool_per_county_nosplit fstate fcounty 
duplicates drop fstate fcounty, force

cd "$OutputPath"

save NCES_nschool_per_county, replace
